Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# NUMTODSINTERVAL

Syntax

Description of the illustration numtodsinterval.gif

Purpose

`NUMTODSINTERVAL` converts `n` to an `INTERVAL` `DAY` `TO` `SECOND` literal. The argument `n` can be any `NUMBER` value or an expression that can be implicitly converted to a `NUMBER` value. The argument `interval_unit` can be of `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` datatype. The value for `interval_unit` specifies the unit of `n` and must resolve to one of the following string values:

- '`DAY`'

- '`HOUR`'

- '`MINUTE`'

- '`SECOND`'

`interval_unit` is case insensitive. Leading and trailing values within the parentheses are ignored. By default, the precision of the return is 9.

See Also: Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion

Examples

The following example uses `NUMTODSINTERVAL` in a `COUNT` analytic function to calculate, for each employee, the number of employees hired by the same manager within the past 100 days from his or her hire date. Please refer to "Analytic Functions" for more information on the syntax of the analytic functions.

    SELECT manager_id, last_name, hire_date,
       COUNT(*) OVER (PARTITION BY manager_id ORDER BY hire_date
       RANGE NUMTODSINTERVAL(100, 'day') PRECEDING) AS t_count
       FROM employees;

    MANAGER_ID LAST_NAME                 HIRE_DATE    T_COUNT
    ---------- ------------------------- --------- ----------
           100 Kochhar                   21-SEP-89          1
           100 De Haan                   13-JAN-93          1
           100 Raphaely                  07-DEC-94          1
           100 Kaufling                  01-MAY-95          1
           100 Hartstein                 17-FEB-96          1
    . . .
           149 Grant                     24-MAY-99          1
           149 Johnson                   04-JAN-00          1
           201 Goyal                     17-AUG-97          1
           205 Gietz                     07-JUN-94          1
               King                      17-JUN-87          1